using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using TVH.Comum;
using System.Data.SqlClient;
using System.Text;

namespace TVH.Base.Persistencia
{
    public static class Db
    {
        // Note: Static initializers are thread safe.
        // If this class had a static constructor then these static variables 
        // would need to be initialized there.
        private static readonly string dataProvider = ConfigurationManager.AppSettings.Get("DataProvider");
        private static readonly DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);

        private static readonly string connectionStringName = ConfigurationManager.AppSettings.Get("ConnectionStringName");
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

        #region Fast data readers


        /// <summary>
        /// Fast read of individual item.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="make"></param>
        /// <param name="commandType"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static T Read<T>(string sql, Func<IDataReader, T> make, CommandType commandType, List<DbParameter> parms, bool autoRead = true)
        {
            GravarLogExecucao(sql, parms);

            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (var command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandType = commandType;
                    command.CommandText = sql;
                    command.SetParameters(parms);  // Extension method

                    connection.Open();

                    T t = default(T);
                    var reader = command.ExecuteReader();
                    if (autoRead && reader.Read())
                        t = make(reader);
                    else
                        t = make(reader);

                    return t;
                }
            }

        }
        /// <summary>
        /// Fast read of individual item.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procName"></param>
        /// <param name="make"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static T Read<T>(string procName, Func<IDataReader, T> make, List<DbParameter> parms, bool autoRead = true)
        {
            return Read<T>(procName, make, CommandType.StoredProcedure, parms, autoRead);
        }
        

        /// <summary>
        /// Fast read of list of items.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="make"></param>
        /// <param name="commandType"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static List<T> ReadList<T>(string sql, Func<IDataReader, T> make, CommandType commandType, List<DbParameter> parms)
        {
            GravarLogExecucao(sql, parms);

            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (var command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;
                    command.CommandType = commandType;
                    command.SetParameters(parms);

                    connection.Open();

                    var list = new List<T>();
                    var reader = command.ExecuteReader();

                    while (reader.Read())
                        list.Add(make(reader));

                    return list;
                }
            }
        }
        /// <summary>
        /// Fast read of list of items.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procName"></param>
        /// <param name="make"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static List<T> ReadList<T>(string procName, Func<IDataReader, T> make, List<DbParameter> parms)
        {
            return ReadList<T>(procName, make, CommandType.StoredProcedure, parms);
        }

        /// <summary>
        /// Gets a record count.
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static int GetCount(string sql, CommandType commandType, List<DbParameter> parms)
        {
            return GetScalar(sql, commandType, parms).AsInt();
        }

        /// <summary>
        /// Gets a record count.
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static int GetCount(string procName, List<DbParameter> parms)
        {
            return GetScalar(procName, parms).AsInt();
        }

        /// <summary>
        /// Gets any scalar value from the database.
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static object GetScalar(string sql, CommandType commandType, List<DbParameter> parms)
        {
            GravarLogExecucao(sql, parms);
            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (var command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;
                    command.CommandType = commandType;
                    command.SetParameters(parms);

                    connection.Open();
                    return command.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// Gets any scalar value from the database.
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static object GetScalar(string procName, List<DbParameter> parms)
        {
            return GetScalar(procName, CommandType.StoredProcedure, parms);
        }

        #endregion

        #region Data update section

        /// <summary>
        /// Inserts an item into the database
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static int Insert(string sql, CommandType commandType, List<DbParameter> parms)
        {
            GravarLogExecucao(sql, parms);

            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (var command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandType = commandType;
                    command.SetParameters(parms);
                    command.CommandText = sql;

                    connection.Open();

                    return command.ExecuteScalar().AsInt();
                }
            }
        }

        /// <summary>
        /// Inserts an item into the database
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static int Insert(string procName, List<DbParameter> parms)
        {
            return Insert(procName, CommandType.StoredProcedure, parms);
        }

        /// <summary>
        /// Updates an item in the database
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        public static void Update(string sql, CommandType commandType, List<DbParameter> parms)
        {
            GravarLogExecucao(sql, parms);

            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (var command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;
                    command.CommandType = commandType;
                    command.SetParameters(parms);

                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Updates an item in the database
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parms"></param>
        public static void Update(string procName, List<DbParameter> parms)
        {
            Update(procName, CommandType.StoredProcedure, parms);
        }
        /// <summary>
        /// Deletes an item from the database.
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        public static void Delete(string sql, List<DbParameter> parms)
        {
            Update(sql, parms);
        }

        #endregion

        #region Extension methods

        /// <summary>
        /// Extension method: Appends the db specific syntax to sql string 
        /// for retrieving newly generated identity (autonumber) value.
        /// </summary>
        /// <param name="sql">The sql string to which to append syntax.</param>
        /// <returns>Sql string with identity select.</returns>
        private static string AppendIdentitySelect(this string sql)
        {
            switch (dataProvider)
            {
                // Microsoft Access does not support multistatement batch commands
                case "System.Data.OleDb": return sql;
                case "System.Data.SqlClient": return sql + ";SELECT SCOPE_IDENTITY()";
                case "System.Data.OracleClient": return sql + ";SELECT MySequence.NEXTVAL FROM DUAL";
                default: return sql + ";SELECT @@IDENTITY";
            }
        }


        public static string[] GetParameterProcedure(string procedureName)
        {
            List<string> ret = new List<string>();
            using (var connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                connection.Open();
                var result = connection.GetSchema("ProcedureParameters");
                var view = result.DefaultView;
                view.RowFilter = string.Format("SPECIFIC_NAME = '{0}'", procedureName);
                foreach (DataRowView item in view)
                {
                    ret.Add(item["PARAMETER_NAME"].AsString());
                }

                return ret.ToArray();
            }

        }

        public static void SetParameters(this DbCommand command, List<DbParameter> parameters)
        {
            if (parameters != null && command != null)
            {
                foreach (DbParameter param in parameters)
                {
                    command.Parameters.Add(param);
                }
            }
        }

        private static void SetParameters(this DbCommand command, object[] parms)
        {
            if (parms != null && parms.Length > 0)
            {
                // NOTE: Processes a name/value pair at each iteration
                for (int i = 0; i < parms.Length; i += 2)
                {
                    string name = parms[i].ToString();

                    // No empty strings to the database
                    if (parms[i + 1] is string && (string)parms[i + 1] == "")
                        parms[i + 1] = null;

                    // If null, set to DbNull
                    object value = parms[i + 1] ?? DBNull.Value;

                    var dbParameter = command.CreateParameter();
                    dbParameter.ParameterName = name;
                    dbParameter.Value = value;

                    command.Parameters.Add(dbParameter);
                }
            }
        }

        public static DbParameter CreateParameter(string name, object value)
        {
            DbParameter param = factory.CreateParameter();
            param.ParameterName = name;
            param.Value = value ?? DBNull.Value;

            return param;
        }

        #endregion

        private static void GravarLogExecucao(string nomeProcedure, List<DbParameter> parms)
        {
            StringBuilder parametros = new StringBuilder();
            parms.ForEach(x => parametros.AppendLine(string.Format("{0}[{1}]:{2}", x.ParameterName, x.DbType, x.Value)));

            var message = string.Format("Execucao da procedure '{0}' com os parametros: {1}" + Environment.NewLine, nomeProcedure, parametros.ToString());


            Log.Logger.Instance.Info(message);
        }
    }
}
